import time
import pymysql


class helper:
    def __init__(self, host, user, password, database, port=3306, charset='utf8mb4'):
        self.connection = None
        self.cursor = None
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset
        self.connect()

    def connect(self):
        """建立数据库连接"""
        self.connection = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database,
            port=self.port,
            charset=self.charset
        )
        self.cursor = self.connection.cursor()  # 初始化游标，缺了这一步会报错

    def reconnect(self):
        """如果连接失效，自动重连"""
        try:
            self.connection.ping(reconnect=True)  # 检查连接是否存活，自动重连
        except Exception as e:
            print("连接丢失，正在重连:", e)
            time.sleep(1)
            self.connect()

    def execute(self, sql, params=None):
        """执行增删改"""
        self.reconnect()
        try:
            self.cursor.execute(sql, params or ())
            self.connection.commit()
            return True
        except Exception as e:
            self.connection.rollback()
            print("SQL 执行失败:", e)
            return False

    def fetchone(self, sql, params=None):
        """查询一条"""
        self.reconnect()
        self.cursor.execute(sql, params or ())
        return self.cursor.fetchone()

    def fetchall(self, sql, params=None):
        """查询多条"""
        self.reconnect()
        self.cursor.execute(sql, params or ())
        return self.cursor.fetchall()

    def close(self):
        """关闭连接"""
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()
        print("数据库连接已关闭")


class UserService:
    def __init__(self, username, password):
        self.username = username
        self.password = password
        self.db = helper('localhost', 'root', 'ak47qbz95', 'hqyj')

    def login(self, username, password):
        l_sql = "SELECT * FROM h_users WHERE username = %s AND password = %s"
        result = self.db.fetchone(l_sql, (username, password))
        if result:
            print("登录成功")
            return True
        else:
            print("用户名或密码错误")
            return False

    def register(self, username, password):
        r_sql = "INSERT INTO h_users (username, password) VALUES (%s, %s)"
        result = self.db.execute(r_sql, (username, password))
        if result:
            print("注册成功")
            return True
        else:
            print("注册失败")
            return False


db = helper('localhost', 'root', 'ak47qbz95', 'hqyj')
sql = """
CREATE TABLE IF NOT EXISTS records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    OverallQual INT,
    GrLivArea INT,
    FirstFlrSF INT,
    TotRmsAbvGrd INT,
    FullBath INT,
    YearBuilt INT,
    GarageCars INT,
    LotArea INT,
    Predict INT,
    FOREIGN KEY (user_id) REFERENCES h_users (id)
);
"""
db.execute(sql)
sql = """
CREATE TABLE IF NOT EXISTS h_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL UNIQUE,
    password VARCHAR(20) NOT NULL
);
"""

db.execute(sql)
db.close()
